<?php
class CambodiaModel extends SBKH{
	public function getProvinces($id = 0){
		$where = "";
		$sql = "SELECT `id`, `province_name`, `alias`, `shape`, `lat_lon`, `color`,
				(SELECT COUNT(di.`id`) FROM " . $this -> GetPrefix('district') . " AS di 
						INNER JOIN " . $this -> GetPrefix('depot') . " AS de
							ON di.`id` = de.`district_id`
						WHERE di.`province_id` = p.`id` LIMIT 1
				) AS has_depot 
				FROM " . $this -> GetPrefix('province') . " AS p";
		if($id != 0){
			$where .= " WHERE `id` = '" . $id . "'";
		}
		$sql .= $where  . " ORDER BY `id` ASC";
		$result = $this -> Query($sql);
		return $result;
	}
	public function getDistricts($proId = 0, $disId = 0){
		$where = "";
		$sql = "SELECT `id`, `province_id`, `alias`, `shape`, `district_name`, `lat_lon`, `color` FROM " . $this -> GetPrefix('district');
		if($proId != 0 && $disId == 0){
			$where .= " WHERE `province_id` ='" . $proId . "'";
		}elseif($proId == 0 && $disId != 0){
			$where .= " WHERE `id` ='" . $disId . "'";
		}else{
			$where .= " WHERE `province_id` ='" . $proId . "' AND `id` ='" . $disId . "'";
		}
		$sql .= $where . " ORDER BY `id` ASC";
		$result = $this -> Query($sql);
		return $result;
	}
	public function getDepots($disId = 0, $depId = 0){
		$where = "";
		$sql = "SELECT `id`, `district_id`, `depot_name`, `alias`, `truck_number`, `tank_number`, `amount_of_sell`, `percentage`, `note`, `modified`,				
						(SELECT `fullname` FROM " . $this -> GetPrefix('users') . " AS u WHERE u.`id` = de.`modified_by` LIMIT 1) AS modified_by 
				FROM " . $this -> GetPrefix('depot') . " AS de";
		if($disId != 0 && $depId == 0){
			$where .= " WHERE `district_id` ='" . $disId . "'";
		}elseif($disId == 0 && $depId != 0){
			$where .= " WHERE `id` ='" . $depId . "'";
		}else{
			$where .= " WHERE `district_id` ='" . $disId . "' AND `id` ='" . $depId . "'";
		}
		$sql .= $where  . " ORDER BY `id` ASC";
		$result = $this -> Query($sql);
		return $result;
	}
	public function getDepotsByProvince($proId = 0){
		$sql = "SELECT de.`id`, de.`district_id`, de.`depot_name`, de.`alias`, de.`truck_number`, de.`tank_number`, de.`amount_of_sell`, de.`percentage`, de.`note`, de.`modified`,
						(SELECT `fullname` FROM " . $this -> GetPrefix('users') . " AS u WHERE u.`id` = de.`modified_by` LIMIT 1) AS modified_by
				FROM " . $this -> GetPrefix('depot') . " AS de
				INNER JOIN " . $this -> GetPrefix('district') . " AS di
						ON de.`district_id` = di.`id`
						INNER JOIN " . $this -> GetPrefix('province') . " AS p 
								ON di.`province_id` = p.`id`
				WHERE p.`id` = '" . $proId . "'";
		
		$sql .=  " ORDER BY de.`id` ASC";
		$result = $this -> Query($sql);
		return $result;
	}
}